using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using FinanceStats.Entities;
using FinanceStats.Common;

namespace FinanceStats.DAL
{
    public static class UserOutcomeDAO
    {
        private static string insertSQL = "INSERT INTO ACS.dbo.UserOutcome (UserID, ItemID, Date, Amount) VALUES (@UserID, @ItemID, @Date, @Amount) SET @UserOutcomeID = SCOPE_IDENTITY()";
        private static string updateSQL = "UPDATE ACS.dbo.UserOutcome SET UserID = @UserID, ItemID = @ItemID, Date = @Date, Amount = @Amount WHERE UserOutcomeID = @UserOutcomeID";
        private static string selectSQL = @"SELECT O1.USEROUTCOMEID, O1.USERID, O1.AMOUNT, O1.DATE, G1.GROUPID, I1.ITEMID, I1.ITEMNAME
                                            FROM ACS.DBO.OUTCOMEGROUP G1 
                                            JOIN ACS.DBO.OUTCOMEITEM I1 ON I1.GROUPID = G1.GROUPID 
                                            JOIN ACS.DBO.USEROUTCOME O1 ON O1.ITEMID = I1.ITEMID {0} ORDER BY I1.ItemName";

        private static string deleteSQL = "DELETE FROM ACS.dbo.UserOutcome WHERE UserOutcomeID = @UserOutcomeID";
        
        private static DbCommand insertCommand = null;
        private static DbCommand updateCommand = null;
        private static DbCommand deleteCommnad = null;

        private static DbCommand GetInsertCommand(SqlDatabase db)
        {
            if (insertCommand == null)
            {
                insertCommand = db.GetSqlStringCommand(insertSQL);
                db.AddInParameter(insertCommand, "@UserID", DbType.Int32);
                db.AddInParameter(insertCommand, "@ItemID", DbType.Int32);
                db.AddInParameter(insertCommand, "@Date", DbType.DateTime);
                db.AddInParameter(insertCommand, "@Amount", DbType.Decimal);
                db.AddOutParameter(insertCommand, "@UserOutcomeID", DbType.Int64, 8);
            }
            return insertCommand;
        }

        private static DbCommand GetUpdateCommand(SqlDatabase db)
        {
            if (updateCommand == null)
            {
                updateCommand = db.GetSqlStringCommand(updateSQL);
                db.AddInParameter(updateCommand, "@UserID", DbType.Int32);
                db.AddInParameter(updateCommand, "@ItemID", DbType.Int32);
                db.AddInParameter(updateCommand, "@Date", DbType.DateTime);
                db.AddInParameter(updateCommand, "@Amount", DbType.Decimal);
                db.AddInParameter(updateCommand, "@UserOutcomeID", DbType.Int64);
            }
            return updateCommand;
        }

        private static DbCommand GetDeleteCommand(SqlDatabase db)
        {
            if (deleteCommnad == null)
            {
                deleteCommnad = db.GetSqlStringCommand(deleteSQL);
                db.AddInParameter(deleteCommnad, "@UserOutcomeID", DbType.Int64);
            }
            return deleteCommnad;
        }

        private static void SetInsertValues(SqlDatabase db, DbCommand cmd, UserOutcome uo)
        {
            db.SetParameterValue(cmd, "@UserID", (int)uo.UserID);
            db.SetParameterValue(cmd, "@ItemID", (int)uo.ItemID);
            db.SetParameterValue(cmd, "@Date", uo.Date);
            db.SetParameterValue(cmd, "@Amount", uo.Amount);
        }

        private static void SetUpdateValues(SqlDatabase db, DbCommand cmd, UserOutcome uo)
        {
            db.SetParameterValue(cmd, "@UserID", (int)uo.UserID);
            db.SetParameterValue(cmd, "@ItemID", (int)uo.ItemID);
            db.SetParameterValue(cmd, "@Date", uo.Date);
            db.SetParameterValue(cmd, "@Amount", uo.Amount);
            db.SetParameterValue(cmd, "@UserOutcomeID", (long)uo.UserOutcomeID);
        }

        private static void SetDeleteValues(SqlDatabase db, DbCommand cmd, UserOutcome uo)
        {
            db.SetParameterValue(cmd, "@UserOutcomeID", (long)uo.UserOutcomeID);
        }

        public static bool InsertUserOutcome(UserOutcome uo)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, uo);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            if (res)
            {
                uo.UserOutcomeID = (long)db.GetParameterValue(cmd, "@UserOutcomeID");
            }
            return res;
        }

        public static bool InsertListUserUoutcome(List<UserOutcome> list)
        {
            			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
                DbCommand cmd = GetInsertCommand(db);
                conn = db.CreateConnection();
                conn.Open();
                transactionObj = conn.BeginTransaction();
                for (int i = 0; i < list.Count; i++)
                {
                    UserOutcome entity = list[i];
                    SetInsertValues(db, cmd, entity);
                    res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
                    if (res)
                    {
                        entity.UserOutcomeID = (long?)db.GetParameterValue(cmd, "@UserOutcomeID");
                    }
                }                
                transactionObj.Commit();
                transactionObj.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                res = false;
                if (transactionObj != null)
                {
                    transactionObj.Rollback();
                    transactionObj.Dispose();
                }
                if (conn != null & conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }
                throw ex;
            }
            return res;
        }


        public static bool UpdateUserOutcome(UserOutcome uo)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
            DbCommand cmd = GetUpdateCommand(db);
            SetUpdateValues(db, cmd, uo);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }

        public static bool DeleteUserOutcome(UserOutcome uo)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
            DbCommand cmd = GetDeleteCommand(db);
            SetDeleteValues(db, cmd, uo);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }

        public static List<UserOutcome> LoadUserOutcomeList(string where)
        {
            List<UserOutcome> res = new List<UserOutcome>();
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
                string select = String.Format(selectSQL, where);
                reader = db.ExecuteReader(CommandType.Text, select);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        UserOutcome uo = new UserOutcome();
                        uo.UserOutcomeID = reader.GetInt64(0);
                        uo.UserID = reader.GetInt32(1);
                        uo.Amount = reader.GetDecimal(2);
                        uo.Date = reader.GetDateTime(3);
                        uo.GroupID = reader.GetInt32(4);
                        uo.ItemID = reader.GetInt32(5);
                        uo.ItemName = reader.GetString(6);
                        res.Add(uo);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                throw ex;
            }
            return res;
        }

        public static DataSet GetTestDataSet()
        {
            string sql = @"
 SELECT UserOutcome.UserID, UserOutcome.ItemID, UserOutcome.Date, UserOutcome.Amount
 FROM   ACS.dbo.UserOutcome UserOutcome
 ORDER BY UserOutcome.ItemID, UserOutcome.UserID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
            return db.ExecuteDataSet(CommandType.Text, sql); 

        }
    }
}
